SELECT¶

FROM Clause¶

The FROM clause specifies the table in which data is to be retrieved in the query. If no table is referenced, the FROM clause can be omitted. Retrieval paths are as follows:

• Single table
• Subquery
• Derived table

SELECT [<qualifier>] <select_expressions>
[
    FROM <table_specification> [ {, <table_specification> | <join_table_specification> }... ]
]

<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>

<table_specification> ::=
    <single_table_spec> [<correlation>] [WITH (<lock_hint>)] |
    <metaclass_specification> [<correlation>] |
    <subquery> <correlation> |
    TABLE (<expression>) <correlation>

<correlation> ::= [AS] <identifier> [(<identifier_comma_list>)]

<single_table_spec> ::= [ONLY] <table_name> |
                      ALL <table_name> [EXCEPT <table_name>]

<metaclass_specification> ::= CLASS <class_name>

<lock_hint> ::= READ UNCOMMITTED

• <select_expressions>: One or more columns or expressions to query is specified. Use * to query all columns in the table. You can also specify an alias for a column or an expression to be queried by using the AS keyword. This keyword can be used in GROUP BY, HAVING and ORDER BY clauses. The position index of the column is given according to the order in which the column was specified. The starting value is 1.
• <table_specification>: At least one table name is specified after the FROM clause. Subqueries and derived tables can also be used in the FROM clause. For details on subquery derived tables, see Subquery Derived Table.
• <lock_hint>: You can set READ UNCOMMITTED for the table isolation level. READ UNCOMMITTED is a level where dirty reads are allowed; see Transaction Isolation Level for details on the CUBRID transaction isolation level.

--FROM clause can be omitted in the statement
SELECT 1+1 AS sum_value;

    sum_value
=============
            2

SELECT CONCAT('CUBRID', '2008' , 'R3.0') AS db_version;

  db_version
======================
  'CUBRID2008R3.0'

FROM (subquery) [AS] [derived_table_name [(column_name [{, column_name } ... ])]]

SELECT SUM (n)
FROM (SELECT gold FROM participant WHERE nation_code = 'KOR'
      UNION ALL
      SELECT silver FROM participant WHERE nation_code = 'JPN') AS t(n);

SELECT nation_code, host_year, gold
FROM participant p
WHERE gold > (SELECT AVG(s)
              FROM (SELECT silver + bronze
                    FROM participant
                    WHERE nation_code = p.nation_code
                    AND silver > 0
                    AND bronze > 0)
                   AS t(s));

  nation_code      host_year      gold
=========================================
  'JPN'                2004         16
  'CHN'                2004         32
  'DEN'                1996          4
  'ESP'                1992         13

WHERE Clause¶

In a query, a column can be processed based on conditions. The WHERE clause specifies a search condition for data.

WHERE <search_condition>

    <search_condition> ::=
        <comparison_predicate>
        <between_predicate>
        <exists_predicate>
        <in_predicate>
        <null_predicate>
        <like_predicate>
        <quantified_predicate>
        <set_predicate>

The WHERE clause specifies a condition that determines the data to be retrieved by search_condition or a query. Only data for which the condition is true is retrieved for the query results. (NULL value is not retrieved for the query results because it is evaluated as unknown value.)

• search_condition: It is described in detail in the following sections.
  • Simple Comparison Expression
  • BETWEEN
  • EXISTS
  • IN
  • IS NULL
  • LIKE
  • ANY/SOME/ALL quantifiers

The logical operator AND or OR can be used for multiple conditions. If AND is specified, all conditions must be true. If OR is specified, only one needs to be true. If the keyword NOT is preceded by a condition, the meaning of the condition is reserved. The following table shows the order in which logical operators are evaluated.

GROUP BY ... HAVING Clause¶

The GROUP BY clause is used to group the result retrieved by the SELECT statement based on a specific column. This clause is used to sort by group or to get the aggregation by group using the aggregation function. Herein, a group consists of records that have the same value for the column specified in the GROUP BY clause.

You can also set a condition for group selection by including the HAVING clause after the GROUP BY clause. That is, only groups satisfying the condition specified by the HAVING clause are queried out of all groups that are grouped by the GROUP BY clause.

By SQL standard, you cannot specify a column (hidden column) not defined in the GROUP BY clause to the SELECT column list. However, by using extended CUBRID grammars, you can specify the hidden column to the SELECT column list. If you do not use the extended CUBRID grammars, the only_full_group_by parameter should be set to yes. For details, see Statement/Type-Related Parameters.

SELECT ...
GROUP BY {col_name | expr | position} [ASC | DESC], ...
          [WITH ROLLUP] [HAVING <search_condition>]

• col_name | expr | position: Specifies one or more column names, expressions, aliases or column location. Items are separated by commas. Columns are sorted on this basis.

• [ASC | DESC]: Specifies the ASC or DESC sorting option after the columns specified in the GROUP BY clause. If the sorting option is not specified, the default value is ASC.

• <search_condition>: Specifies the search condition in the HAVING clause. In the HAVING clause, you can refer to columns and aliases specified in the GROUP BY clause, or columns used in aggregate functions.

  Note

  Even the hidden columns not specified in the GROUP BY clause can be referred to, if the value of the only_full_group_by parameter is set to yes. At this time, the HAVING condition does not affect to the query result.

• WITH ROLLUP: If you specify the WITH ROLLUP modifier in the GROUP BY clause, the aggregate information of the result value of each GROUPed BY column is displayed for each group, and the total of all result rows is displayed at the last row. When a WITH ROLLUP modifier is defined in the GROUP BY clause, the result value for all rows of the group is additionally displayed. In other words, total aggregation is made for the value aggregated by group. When there are two columns for Group By, the former is considered as a large unit and the latter is considered as a small unit, so the total aggregation row for the small unit and the total aggregation row for the large unit are added. For example, you can check the aggregation of the sales result per department and salesperson through one query.

-- creating a new table
CREATE TABLE sales_tbl
(dept_no INT, name VARCHAR(20), sales_month INT, sales_amount INT DEFAULT 100, PRIMARY KEY (dept_no, name, sales_month));

INSERT INTO sales_tbl VALUES
(201, 'George' , 1, 450), (201, 'George' , 2, 250), (201, 'Laura'  , 1, 100), (201, 'Laura'  , 2, 500),
(301, 'Max'    , 1, 300), (301, 'Max'    , 2, 300),
(501, 'Stephan', 1, 300), (501, 'Stephan', 2, DEFAULT), (501, 'Chang'  , 1, 150),(501, 'Chang'  , 2, 150),
(501, 'Sue'    , 1, 150), (501, 'Sue'    , 2, 200);

-- selecting rows grouped by dept_no
SELECT dept_no, avg(sales_amount)
FROM sales_tbl
GROUP BY dept_no;

      dept_no         avg(sales_amount)
=======================================
          201     3.250000000000000e+02
          301     3.000000000000000e+02
          501     1.750000000000000e+02

-- conditions in WHERE clause operate first before GROUP BY
SELECT dept_no, avg(sales_amount)
FROM sales_tbl
WHERE sales_amount > 100
GROUP BY dept_no;

      dept_no         avg(sales_amount)
=======================================
          201     4.000000000000000e+02
          301     3.000000000000000e+02
          501     1.900000000000000e+02

-- conditions in HAVING clause operate last after GROUP BY
SELECT dept_no, avg(sales_amount)
FROM sales_tbl
WHERE sales_amount > 100
GROUP BY dept_no HAVING avg(sales_amount) > 200;

      dept_no         avg(sales_amount)
=======================================
          201     4.000000000000000e+02
          301     3.000000000000000e+02

-- selecting and sorting rows with using column alias
SELECT dept_no AS a1, avg(sales_amount) AS a2
FROM sales_tbl
WHERE sales_amount > 200 GROUP
BY a1 HAVING a2 > 200
ORDER BY a2;

           a1                        a2
=======================================
          301     3.000000000000000e+02
          501     3.000000000000000e+02
          201     4.000000000000000e+02

-- selecting rows grouped by dept_no, name with WITH ROLLUP modifier
SELECT dept_no AS a1, name AS a2, avg(sales_amount) AS a3
FROM sales_tbl
WHERE sales_amount > 100
GROUP BY a1, a2 WITH ROLLUP;

           a1  a2                                          a3
=============================================================
          201  'George'                 3.500000000000000e+02
          201  'Laura'                  5.000000000000000e+02
          201  NULL                     4.000000000000000e+02
          301  'Max'                    3.000000000000000e+02
          301  NULL                     3.000000000000000e+02
          501  'Chang'                  1.500000000000000e+02
          501  'Stephan'                3.000000000000000e+02
          501  'Sue'                    1.750000000000000e+02
          501  NULL                     1.900000000000000e+02
         NULL  NULL                     2.750000000000000e+02

ORDER BY Clause¶

The ORDER BY clause sorts the query result set in ascending or descending order. If you do not specify a sorting option such as ASC or DESC, the result set in ascending order by default. If you do not specify the ORDER BY clause, the order of records to be queried may vary depending on query.

SELECT ...
ORDER BY {col_name | expr | position} [ASC | DESC], ...] [NULLS {FIRST | LAST}]

• col_name | expr | position: Specifies a column name, expression, alias, or column location. One or more column names, expressions or aliases can be specified. Items are separated by commas. A column that is not specified in the list of SELECT columns can be specified.
• [ASC | DESC]: ASC means sorting in ascending order, and DESC is sorting in descending order. If the sorting option is not specified, the default value is ASC.
• [NULLS {FIRST | LAST}]: NULLS FIRST sorts NULL at first, NULLS LAST sorts NULL at last. If this syntax is omitted, ASC sorts NULL at first, DESC sorts NULL at last.

-- selecting rows sorted by ORDER BY clause
SELECT *
FROM sales_tbl
ORDER BY dept_no DESC, name ASC;

      dept_no  name                  sales_month  sales_amount
==============================================================
          501  'Chang'                         1           150
          501  'Chang'                         2           150
          501  'Stephan'                       1           300
          501  'Stephan'                       2           100
          501  'Sue'                           1           150
          501  'Sue'                           2           200
          301  'Max'                           1           300
          301  'Max'                           2           300
          201  'George'                        1           450
          201  'George'                        2           250
          201  'Laura'                         1           100
          201  'Laura'                         2           500

-- sorting reversely and limiting result rows by LIMIT clause
SELECT dept_no AS a1, avg(sales_amount) AS a2
FROM sales_tbl
GROUP BY a1
ORDER BY a2 DESC
LIMIT 3;

           a1           a2
=======================================
          201     3.250000000000000e+02
          301     3.000000000000000e+02
          501     1.750000000000000e+02

The following is an example how to specify the NULLS FIRST or NULLS LAST after ORDER BY clause.

CREATE TABLE tbl (a INT, b VARCHAR);

INSERT INTO tbl VALUES
(1,NULL), (2,NULL), (3,'AB'), (4,NULL), (5,'AB'),
(6,NULL), (7,'ABCD'), (8,NULL), (9,'ABCD'), (10,NULL);

SELECT * FROM tbl ORDER BY b NULLS FIRST;

            a  b
===================================
            1  NULL
            2  NULL
            4  NULL
            6  NULL
            8  NULL
           10  NULL
            3  'ab'
            5  'ab'
            7  'abcd'
            9  'abcd'

SELECT * FROM tbl ORDER BY b NULLS LAST;

            a  b
===================================
            3  'ab'
            5  'ab'
            7  'abcd'
            9  'abcd'
            1  NULL
            2  NULL
            4  NULL
            6  NULL
            8  NULL
           10  NULL

CREATE TABLE t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,NULL,2);
INSERT INTO t1 VALUES(2,2,2);

SELECT a, NVL(b,2) AS b
FROM t1
GROUP BY a, b;  -- Q1

LIMIT Clause¶

The LIMIT clause can be used to limit the number of records displayed. You can specify a very big integer for row_count to display to the last row, starting from a specific row. The LIMIT clause can be used as a prepared statement. In this case, the bind parameter (?) can be used instead of an argument.

INST_NUM () and ROWNUM cannot be included in the WHERE clause in a query that contains the LIMIT clause. Also, LIMIT cannot be used together with HAVING GROUPBY_NUM ().

LIMIT {[offset,] row_count | row_count [OFFSET offset]}

• offset: Specifies the offset value of the starting row to be displayed. The offset value of the starting row of the result set is 0; it can be omitted and the default value is 0.
• row_count: Specifies the number of records to be displayed. You can specify an integer greater than 0.

-- LIMIT clause can be used in prepared statement
PREPARE stmt FROM 'SELECT * FROM sales_tbl LIMIT ?, ?';
EXECUTE stmt USING 0, 10;

-- selecting rows with LIMIT clause
SELECT *
FROM sales_tbl
WHERE sales_amount > 100
LIMIT 5;

      dept_no  name                  sales_month  sales_amount
==============================================================
          201  'George'                        1           450
          201  'George'                        2           250
          201  'Laura'                         2           500
          301  'Max'                           1           300
          301  'Max'                           2           300

-- LIMIT clause can be used in subquery
SELECT t1.*
FROM (SELECT * FROM sales_tbl AS t2 WHERE sales_amount > 100 LIMIT 5) AS t1
LIMIT 1,3;

-- above query and below query shows the same result
SELECT t1.*
FROM (SELECT * FROM sales_tbl AS t2 WHERE sales_amount > 100 LIMIT 5) AS t1
LIMIT 3 OFFSET 1;

      dept_no  name                  sales_month  sales_amount
==============================================================
          201  'George'                        2           250
          201  'Laura'                         2           500
          301  'Max'                           1           300

Join Query¶

A join is a query that combines the rows of two or more tables or virtual tables (views). In a join query, a condition that compares the columns that are common in two or more tables is called a join condition. Rows are retrieved from each joined table, and are combined only when they satisfy the specified join condition.

A join query using an equality operator (=) is called an equi-join, and one without any join condition is called a cartesian product. Meanwhile, joining a single table is called a self join. In a self join, table ALIAS is used to distinguish columns, because the same table is used twice in the FROM clause.

A join that outputs only rows that satisfy the join condition from a joined table is called an inner or a simple join, whereas a join that outputs both rows that satisfy and do not satisfy the join condition from a joined table is called an outer join.

An outer join is divided into a left outer join which outputs all rows of the left table as a result(outputs NULL when the right table's columns don't match conditions), a right outer join which outputs all rows of the right table as a result(outputs NULL when the left table's columns don't match conditions) and a full outer join which outputs all rows of both tables. If there is no column value that corresponds to a table on one side in the result of an outer join query, all rows are returned as NULL.

FROM <table_specification> [{, <table_specification>
    | { <join_table_specification> | <join_table_specification2> } ...]

<table_specification> ::=
    <single_table_spec> [<correlation>] [WITH (<lock_hint>)]|
    <metaclass_specification> [<correlation>] |
    <subquery> <correlation> |
    TABLE (<expression>) <correlation>

<join_table_specification> ::=
    [INNER | {LEFT | RIGHT} [OUTER]] JOIN <table_specification> ON <search_condition>

<join_table_specification2> ::=
    CROSS JOIN table_specification

• <join_table_specification>
  • [INNER] JOIN: Used for inner join and requires join conditions.
  • {LEFT | RIGHT} [OUTER] JOIN: LEFT is used for a left outer join query, and RIGHT is for a right outer join query.
• <join_table_specification2>
  • CROSS JOIN: Used for cross join and requires no join conditions.

SELECT DISTINCT h.host_year, o.host_nation
FROM history h INNER JOIN olympic o ON h.host_year = o.host_year AND o.host_year > 1950;

SELECT DISTINCT h.host_year, o.host_nation
FROM history h, olympic o
WHERE h.host_year = o.host_year AND o.host_year > 1950;

    host_year  host_nation
===================================
         1968  'Mexico'
         1980  'U.S.S.R.'
         1984  'United States of America'
         1988  'Korea'
         1992  'Spain'
         1996  'United States of America'
         2000  'Australia'
         2004  'Greece'

SELECT DISTINCT h.host_year, o.host_year, o.host_nation
FROM history h RIGHT OUTER JOIN olympic o ON h.host_year = o.host_year
WHERE o.host_year > 1950;

    host_year    host_year  host_nation
================================================
         NULL         1952  'Finland'
         NULL         1956  'Australia'
         NULL         1960  'Italy'
         NULL         1964  'Japan'
         NULL         1972  'Germany'
         NULL         1976  'Canada'
         1968         1968  'Mexico'
         1980         1980  'USSR'
         1984         1984  'USA'
         1988         1988  'Korea'
         1992         1992  'Spain'
         1996         1996  'USA'
         2000         2000  'Australia'
         2004         2004  'Greece'

SELECT DISTINCT h.host_year, o.host_year, o.host_nation
FROM olympic o LEFT OUTER JOIN history h ON h.host_year = o.host_year
WHERE o.host_year > 1950;

    host_year    host_year  host_nation
================================================
         NULL         1952  'Finland'
         NULL         1956  'Australia'
         NULL         1960  'Italy'
         NULL         1964  'Japan'
         NULL         1972  'Germany'
         NULL         1976  'Canada'
         1968         1968  'Mexico'
         1980         1980  'USSR'
         1984         1984  'USA'
         1988         1988  'Korea'
         1992         1992  'Spain'
         1996         1996  'USA'
         2000         2000  'Australia'
         2004         2004  'Greece'

SELECT DISTINCT h.host_year, o.host_year, o.host_nation
FROM history h, olympic o
WHERE o.host_year = h.host_year(+) AND o.host_year > 1950;

    host_year    host_year  host_nation
================================================
         NULL         1952  'Finland'
         NULL         1956  'Australia'
         NULL         1960  'Italy'
         NULL         1964  'Japan'
         NULL         1972  'Germany'
         NULL         1976  'Canada'
         1968         1968  'Mexico'
         1980         1980  'USSR'
         1984         1984  'USA'
         1988         1988  'Korea'
         1992         1992  'Spain'
         1996         1996  'USA'
         2000         2000  'Australia'
         2004         2004  'Greece'

SELECT DISTINCT h.host_year, o.host_year, o.host_nation
FROM olympic o LEFT OUTER JOIN history h ON h.host_year = o.host_year AND o.host_year > 1950;

    host_year    host_year  host_nation
================================================
         NULL         1896  'Greece'
         NULL         1900  'France'
         NULL         1904  'USA'
         NULL         1908  'United Kingdom'
         NULL         1912  'Sweden'
         NULL         1920  'Belgium'
         NULL         1924  'France'
         NULL         1928  'Netherlands'
         NULL         1932  'USA'
         NULL         1936  'Germany'
         NULL         1948  'England'
         NULL         1952  'Finland'
         NULL         1956  'Australia'
         NULL         1960  'Italy'
         NULL         1964  'Japan'
         NULL         1972  'Germany'
         NULL         1976  'Canada'
         1968         1968  'Mexico'
         1980         1980  'USSR'
         1984         1984  'USA'
         1988         1988  'Korea'
         1992         1992  'Spain'
         1996         1996  'USA'
         2000         2000  'Australia'
         2004         2004  'Greece'

SELECT DISTINCT h.host_year, o.host_nation
FROM history h CROSS JOIN olympic o;

SELECT DISTINCT h.host_year, o.host_nation
FROM history h, olympic o;

    host_year  host_nation
===================================
         1968  'Australia'
         1968  'Belgium'
         1968  'Canada'
         1968  'England'
         1968  'Finland'
         1968  'France'
         1968  'Germany'
...
         2004  'Spain'
         2004  'Sweden'
         2004  'USA'
         2004  'USSR'
         2004  'United Kingdom'

144 rows selected. (1.283548 sec) Committed.

Subquery¶

A subquery can be used wherever expressions such as SELECT or WHERE clause can be used. If the subquery is represented as an expression, it must return a single column; otherwise it can return multiple rows. Subqueries can be divided into single-row subquery and multiple-row subquery depending on how they are used.

SELECT h.host_year, (SELECT host_nation FROM olympic o WHERE o.host_year=h.host_year) AS host_nation,
       h.event_code, h.score, h.unit
FROM history h;

    host_year  host_nation            event_code  score                 unit
============================================================================================
    2004       'Greece'               20283       '07:53.0'             'time'
    2004       'Greece'               20283       '07:53.0'             'time'
    2004       'Greece'               20281       '03:57.0'             'time'
    2004       'Greece'               20281       '03:57.0'             'time'
    2004       'Greece'               20281       '03:57.0'             'time'
    2004       'Greece'               20281       '03:57.0'             'time'
    2004       'Greece'               20326       '210'                 'kg'
    2000       'Australia'            20328       '225'                 'kg'
    2004       'Greece'               20331       '237.5'               'kg'
...

SELECT name, capital, list(SELECT host_city FROM olympic WHERE host_nation = name) AS host_cities
FROM nation;

  name                      capital                 host_cities
==================================================================
  'Somalia'                   'Mogadishu'           {}
  'Sri Lanka'                 'Sri Jayewardenepura Kotte' {}
  'Sao Tome & Principe'       'Sao Tome'            {}
  ...
  'U.S.S.R.'                  'Moscow'              {'Moscow'}
  'Uruguay'                   'Montevideo'          {}
  'United States of America'  'Washington.D.C'      {'Atlanta ', 'St. Louis', 'Los Angeles', 'Los Angeles'}
  'Uzbekistan'                'Tashkent'            {}
  'Vanuatu'                   'Port Vila'           {}

VALUES¶

The VALUES clause prints out the values of rows defined in the expression. In most cases, the VALUES clause is used for creating a constant table, however, the clause itself can be used. When one or more rows are specified in the VALUES clause, all rows should have the same number of the elements.

VALUES (expression[, ...])[, ...]

• expression : An expression enclosed within parentheses stands for one row in a table.

The VALUES clause can be used to express the UNION ALL query, which consists of constant values in a simpler way. For example, the following query can be executed.

VALUES (1 AS col1, 'first' AS col2), (2, 'second'), (3, 'third'), (4, 'fourth');

The above query prints out the following result.

SELECT 1 AS col1, 'first' AS col2
UNION ALL
SELECT 2, 'second'
UNION ALL
SELECT 3, 'third'
UNION ALL
SELECT 4, 'fourth';

The following example shows use of the VALUES clause with multiple rows in the INSERT statement.

INSERT INTO athlete (code, name, gender, nation_code, event)
VALUES ('21111', 'Jang Mi-Ran ', 'F', 'KOR', 'Weight-lifting'),
       ('21112', 'Son Yeon-Jae ', 'F', 'KOR', 'Rhythmic gymnastics');

The following example shows how to use subquery in the FROM statement.

SELECT a.*
FROM athlete a, (VALUES ('Jang Mi-Ran', 'F'), ('Son Yeon-Jae', 'F')) AS t(name, gender)
WHERE a.name=t.name AND a.gender=t.gender;

         code  name                gender   nation_code        event
=====================================================================================================
        21111  'Jang Mi-Ran'       'F'      'KOR'              'Weight-lifting'
        21112  'Son Yeon-Jae'      'F'      'KOR'              'Rhythmic gymnastics'

FOR UPDATE¶

The FOR UPDATE clause can be used in SELECT statements for locking rows returned by the statement for a later UPDATE/DELETE.

SELECT ... [FOR UPDATE [OF <spec_name_comma_list>]]

    <spec_name_comma_list> ::= <spec_name> [, <spec_name>, ... ]
        <spec_name> ::= table_name | view_name

• <spec_name_comma_list>: A list of table/view names referenced from the FROM clause.

Only table/view referenced in <spec_name_comma_list> will be locked. If the <spec_name_comma_list> is missing but FOR UPDATE is present then we assume that all tables/views from the FROM clause of the SELECT statement are referenced.

The locks which have been acquired by FOR UPDATE clause are released in the UPDATE/DELETE statement. When SELECT .. FOR UPDATE is being executed, the locks on keys aren't acquired.

The following shows how to use SELECT ... FOR UPDATE statements.

CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);

CREATE TABLE t2(i INT);
INSERT INTO t2 VALUES (1), (2), (3), (4), (5);
CREATE INDEX idx_t2_i ON t2(i);

CREATE VIEW v12 AS SELECT t1.i AS i1, t2.i AS i2 FROM t1 INNER JOIN t2 ON t1.i=t2.i;

SELECT * FROM t1 ORDER BY 1 FOR UPDATE;
SELECT * FROM t1 ORDER BY 1 FOR UPDATE OF t1;
SELECT * FROM t1 INNER JOIN t2 ON t1.i=t2.i ORDER BY 1 FOR UPDATE OF t1, t2;

SELECT * FROM t1 INNER JOIN (SELECT * FROM t2 WHERE t2.i > 0) r ON t1.i=r.i WHERE t1.i > 0 ORDER BY 1 FOR UPDATE;

SELECT * FROM v12 ORDER BY 1 FOR UPDATE;
SELECT * FROM t1, (SELECT * FROM v12, t2 WHERE t2.i > 0 AND t2.i=v12.i1) r WHERE t1.i > 0 AND t1.i=r.i ORDER BY 1 FOR UPDATE OF r;